﻿Imports System.Data.OleDb
Module XL_ChiCoDinh
    Structure ChiCoDinh
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _ngay As Integer
        Property Ngay() As Integer
            Get
                Return _ngay
            End Get
            Set(ByVal value As Integer)
                _ngay = value
            End Set
        End Property
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        Private _loaiChi As Integer
        Property LoaiChi() As Integer
            Get
                Return _loaiChi
            End Get
            Set(ByVal value As Integer)
                _loaiChi = value
            End Set
        End Property
        Private _taiKhoan As Integer
        Property TaiKhoan() As Integer
            Get
                Return _taiKhoan
            End Get
            Set(ByVal value As Integer)
                _taiKhoan = value
            End Set
        End Property
        Private _nguoiChi As Integer
        Property NguoiChi() As Integer
            Get
                Return _nguoiChi
            End Get
            Set(ByVal value As Integer)
                _nguoiChi = value
            End Set
        End Property
        Private _chiTiet As String
        Property ChiTiet() As String
            Get
                Return _chiTiet
            End Get
            Set(ByVal value As String)
                _chiTiet = value
            End Set
        End Property
        Private _lanCuoi As Date
        Property LanCuoi() As Date
            Get
                Return _lanCuoi
            End Get
            Set(ByVal value As Date)
                _lanCuoi = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal ngchi As Integer) As List(Of ChiCoDinh)
        Dim ds As New List(Of ChiCoDinh)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from ChiCoDinh where NguoiChi=@NguoiChi", con)
            cmd.Parameters.AddWithValue("@NguoiChi", ngchi)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As ChiCoDinh = New ChiCoDinh()
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetInt32(1)
                p.SoTien = rd.GetInt32(2)
                p.LoaiChi = rd.GetInt32(3)
                p.TaiKhoan = rd.GetInt32(4)
                p.NguoiChi = rd.GetInt32(5)
                p.ChiTiet = rd.GetString(6)
                p.LanCuoi = rd.GetDateTime(7)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As ChiCoDinh
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from ChiCoDinh where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As ChiCoDinh = New ChiCoDinh()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetInt32(1)
                p.SoTien = rd.GetInt32(2)
                p.LoaiChi = rd.GetInt32(3)
                p.TaiKhoan = rd.GetInt32(4)
                p.NguoiChi = rd.GetInt32(5)
                p.ChiTiet = rd.GetString(6)
                p.LanCuoi = rd.GetDateTime(7)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByRef p As ChiCoDinh) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into chicodinh(Ngay, SoTien, LoaiChi,TaiKhoan,NguoiChi,ChiTiet,LanCuoi) values(@Ngay, @SoTien, @LoaiChi,@TaiKhoan,@NguoiChi,@ChiTiet,@LanCuoi)", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LoaiChi", p.LoaiChi)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiChi", p.NguoiChi)
            cmd.Parameters.AddWithValue("@ChiTiet", p.ChiTiet)
            cmd.Parameters.AddWithValue("@LanCuoi", p.LanCuoi)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "Select @@Identity"
            p.ID = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As ChiCoDinh) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from ChiCoDinh where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As ChiCoDinh) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update ChiCoDinh set Ngay=@Ngay, SoTien=@SoTien, LoaiChi=@LoaiChi,TaiKhoan=@TaiKhoan,NguoiChi=@NguoiChi,ChiTiet=@ChiTiet,LanCuoi=@LanCuoi where ID = @ID", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@LoaiChi", p.LoaiChi)
            cmd.Parameters.AddWithValue("@TaiKhoan", p.TaiKhoan)
            cmd.Parameters.AddWithValue("@NguoiChi", p.NguoiChi)
            cmd.Parameters.AddWithValue("@ChiTiet", p.ChiTiet)
            cmd.Parameters.AddWithValue("@LanCuoi", p.LanCuoi)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Function TuDongPhatSinh(ByVal user As Integer) As Integer
        Dim ds = XL_ChiCoDinh.LayDS(user)

        Dim kq As Integer = 0
        For Each c In ds
            Dim ngayps = New Date(Now.Year, Now.Month, c.Ngay)
            If c.LanCuoi <> ngayps Then
                Dim count = DateDiff(DateInterval.Month, c.LanCuoi, Now.Date)
                kq += count
                While count > 0
                    Dim thang = Now.Month - count + 1
                    Dim nam = Now.Year
                    If thang < 0 Then
                        thang += 12
                        nam -= 1
                    End If
                    Dim p As New PhieuChi
                    p.Ngay = New Date(nam, thang, c.Ngay)
                    p.LoaiChi = c.LoaiChi
                    p.NguoiChi = c.NguoiChi
                    p.NguoiLap = c.NguoiChi
                    p.SoTien = c.SoTien
                    p.TaiKhoan = c.TaiKhoan
                    p.ChiTiet = c.ChiTiet
                    XL_PhieuChi.Them(p)
                    count -= 1
                End While
                c.LanCuoi = ngayps
                XL_ChiCoDinh.CapNhat(c)
            End If
        Next
        Return kq
    End Function
End Module

